<?php
/**
* Copyright 2009 Waag Society <http://www.waag.org>
* This file is part of Annotrax.
*
* Annotrax is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License,
* or (at your option) any later version.
*
* Annotrax is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
* See the GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with Annotrax.
* If not, see http://www.gnu.org/licenses/gpl.html
*/
class Default_Model_SearchMapper{
	protected $dbTables;

	/**
	 * Select all movies with a title that matches one or more words in the query
	 *
	 * @param Default_Model_Search $search to use for query/limit/offset
	 */
	public function movieSearch( Default_Model_Search $search ){
		// @todo the movie search could be improved to search for single
		// words just like annotation search
		$collect = array();

		$words = explode( ' ', $search->query );

		$select = $this->getTable()->select();
		$select->limit( $search->getLimit(), $search->getOffset() );

		foreach( $words as $word ){
			$select->orWhere( 'title LIKE ?', "%$word%" );
		}

		$result = $this->getTable()->fetchAll( $select );

		foreach( $result as $row ){
			$collect[] = new Default_Model_Movie( $row->toArray() );
		}

		return $collect;
	}

	/**
	 * Select all annotations from the database that have comments or keywords
	 * matching one or more words in the search query.
	 * Ordering by match-count
	 *
	 * @param Default_Model_Search $search model for query, limit and offset
	 */
	public function annotationSearch( Default_Model_Search $search ){
		$collect = array();
		$where = array();
		$bind = array();
		$words = explode( ' ', $search->query );

		//var_dump( $this->getTable( 'annotation' ) );exit;

		$db = $this->getTable('annotation')->getAdapter();

		foreach( $words as $i => $word ){
			$word = sprintf( '%%%s%%', $word );
			$where[] = sprintf('a.comment LIKE :word_%d', $i);
			$where[] = sprintf('k.name LIKE :name_%d', $i);
			$bind[sprintf(':word_%d', $i )] = $word;
			$bind[sprintf(':name_%d', $i )] = $word;
		}

		$query = sprintf('
			SELECT a.*, count(a.id) AS score
			FROM annotation a
			LEFT JOIN annotation_keyword ak ON ak.annotation_id = a.id
			LEFT JOIN keyword k ON ak.keyword_id = k.id
			WHERE %s
			GROUP BY a.id
			ORDER BY score DESC
		',
		join( ' OR ', $where ));

		// @todo find a way to do this the Zend like way
		if( (int) $search->getLimit() > 0 ){
			$query .= sprintf(' LIMIT %d ', $search->getLimit() );
		}

		if( (int) $search->getOffset() > 0 ){
			$query .= sprintf( ' OFFSET %d', $search->getOffset() );
		}

		$result = $db->fetchAll( $query, $bind, Zend_Db::FETCH_ASSOC );

		foreach( $result as $i => $value ){
			unset( $value['score'] );
			$result[$i] = new Default_Model_Annotation( $value );
		}

		return $result;
	}

	/**
	 * return the total result count for current search
	 *@todo testing...
	 * @param Default_Model_Search $search The search model to use.
	 */
	public function getCount( Default_Model_Search $search ){
		$mode 	= $search->queryMode;
		$values = array();
		$where = array();
		$words = explode( ' ', $search->query );

		$db = $this->getTable('annotation')->getAdapter();

		switch( $mode ){
			case 'movie':
				foreach( $words as $i => $word ){
					$word = sprintf( '%%%s%%', $word );
					$where[] = sprintf('title LIKE :title_%d', $i);
					$values[sprintf(':title_%d', $i )] = $word;
				}

				$query = sprintf('
					SELECT COUNT(movie.id) AS total
					FROM movie
					WHERE %s
				', join( ' OR ', $where ));

				$result = $db->fetchRow( $query, $values, Zend_Db::FETCH_OBJ );
				return $result->total;
				break;
			case 'annotation':
				foreach( $words as $i => $word ){
					$word = sprintf( '%%%s%%', $word );
					$where[] = sprintf('a.comment LIKE :word_%d', $i);
					$where[] = sprintf('k.name LIKE :name_%d', $i);
					$values[sprintf(':word_%d', $i )] = $word;
					$values[sprintf(':name_%d', $i )] = $word;
				}

				$query = sprintf('
					SELECT DISTINCT count(a.id) AS total
					FROM annotation a
					LEFT JOIN annotation_keyword ak ON ak.annotation_id = a.id
					LEFT JOIN keyword k ON ak.keyword_id = k.id
					WHERE %s
				', join( "\nOR ", $where ));


				$result = $db->fetchRow( $query, $values, Zend_Db::FETCH_OBJ);

				return $result->total;
				break;
		}
	}

	public function fetchMoviesById( $ids ){
	//	var_dump( $ids );
		$result = $this->getTable()->fetchAll(
			$this->getTable()->select()->where('id IN (?)', $ids ),
			Zend_Db::FETCH_ASSOC
		);

		$collect = array();

		foreach( $result as $values ){
			$collect[] = new Default_Model_Movie( $values->toArray() );
		}

		return $collect;
	}

	public function fetchChannelsById( $ids ){
		$table 	= $this->getTable( 'channel' );
		$select	= $table->select()->where( 'id IN (?)', $ids );

		$result = $table->fetchAll( $select, Zend_Db::FETCH_ASSOC );

		$collect = array();
		foreach( $result as $values ){
			$collect[] = new Default_Model_Channel( $values->toArray() );
		}

		return $collect;
	}

	private function getTable( $name = 'movie' ){
		if( null == $this->dbTables || ! isset( $this->dbTables[$name] ) ){
			$this->setTable( $name );
		}

		return $this->dbTables[$name];
	}

	private function setTable( $name ){
		if( null == $this->dbTables ){
			$this->dbTables = array();
		}

		$this->dbTables[$name] = new Zend_Db_Table( $name );
	}
}
